package db.tables;


import java.sql.Statement;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import control.ConnectionManage;
import db.parking.beans.Parking;
import db.parking.beans.rates;


/**
 * AdminParking manages all requests From,To table Parking
 * @author Operations
 *
 */
public class AdminParking {
	
	private static Connection conn = ConnectionManage.getInstance().getConnection();

	
	public static Parking getRow(String carNo) throws SQLException {

		String sql = "SELECT * FROM Parking WHERE carNo = ?";
		ResultSet rs = null;

		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			stmt.setString(1, carNo);
			rs = stmt.executeQuery();

			if (rs.next()) {
				Parking bean = new Parking();
				bean.setLotNo(rs.getInt("lotNo"));
				bean.setCarNo(rs.getString("carNo"));
				bean.setFloorNo(rs.getInt("floorNo"));
				bean.setPositionX(rs.getInt("positionX"));
				bean.setPositionY(rs.getInt("positionY"));
				bean.setStatus(rs.getInt("status"));
				return bean;
			} else {
				return null;
			}

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}
	
	/**
	 * Functions returns all rows or parking DB table into an array
	 * @return
	 * @throws SQLException
	 */
	public static Parking[] getRows() throws SQLException {

		String sql = "SELECT * FROM Parking WHERE status <> 0";
		ResultSet rs = null;

		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			rs = stmt.executeQuery();

			int rowCount = 0;
			rs.last();
			int totalRows = rs.getRow();
			rs.beforeFirst();
			Parking bean[] = new Parking[totalRows];
			for(int i=0;i<totalRows;i++)
				bean[i] = new Parking(); 
			
			while (rs.next()) {
				bean[rowCount].setLotNo(rs.getInt("LotNo"));
				bean[rowCount].setCarNo(rs.getString("carNo"));
				bean[rowCount].setFloorNo(rs.getInt("floorNo"));
				bean[rowCount].setPositionX(rs.getInt("positionX"));
				bean[rowCount].setPositionY(rs.getInt("positionY"));
				bean[rowCount].setStatus(rs.getInt("status"));
				rowCount++;
			} 
			return bean;

		} catch (SQLException e) {
			System.err.println(e);
			return null;
		} finally {
			if (rs != null) {
				rs.close();
			}
		}

	}

	public static boolean insert(Parking bean) throws Exception {
		String sql = "INSERT into Parking (lotNo , carNo, floorNo , positionX , positionY , status) " +
				"VALUES (?, ?, ?, ?, ?, ?)";
		ResultSet keys = null;
		try (
				PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
				) {
			
			stmt.setInt(1, bean.getLotNo());
			stmt.setString(2, bean.getCarNo());
			stmt.setInt(3, bean.getFloorNo());
			stmt.setInt(4, bean.getPositionX());
			stmt.setInt(5, bean.getPositionY());
			stmt.setInt(6, bean.getStatus());
			stmt.executeUpdate();
			
		} catch (SQLException e) {
			System.err.println(e);
			return false;
		} finally{
			if(keys != null)
				keys.close();
		}
		return true;
		
	}
	/**
	 * Functions updates parking position
	 * @param bean
	 * @return
	 * @throws Exception
	 */
	public static boolean update(Parking bean) throws Exception {

		String sql =
				"UPDATE Parking SET " +
				"status = ? " +
				"WHERE positionX = ? AND positionY = ? AND floorNo = ?";
		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			
			stmt.setInt(1,bean.getStatus());
			stmt.setInt(2,bean.getPositionX());
			stmt.setInt(3,bean.getPositionY());	
			stmt.setInt(4,bean.getFloorNo());	

			
			int affected = stmt.executeUpdate();
			if(affected == 1){
				return true;
			}else{
				return false;
			}
			
		}
		catch(SQLException e) {
			System.err.println(e);
			return false;
		}

	}
     
	
	
     
	
	
	public static boolean delete(int lotID,int x,int y,int z) throws Exception {

		String sql = "DELETE FROM parking WHERE lotNo = ? AND positionX =?  AND positionY = ? AND floorNo =?";
		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			stmt.setInt(1,lotID);
			stmt.setInt(2,x);
			stmt.setInt(3,y);
			stmt.setInt(4,z);

			int affected = stmt.executeUpdate();
			if(affected == 1){
					return true;
			} else {
				return false;
			}
		}
		catch(SQLException e) {
			System.err.println(e);
			return false;
		}

	}
	

	/**
	 * Functions clears Lot completely from all cars
	 * @param lotId
	 * @return
	 * @throws Exception
	 */
	public static boolean deleteAllLocastions(int lotId) throws Exception {

		String sql = "DELETE FROM parking WHERE lotNo=? AND status=1";
		try (
				PreparedStatement stmt = conn.prepareStatement(sql);
				){
			stmt.setInt(1,lotId);
			int affected = stmt.executeUpdate();
			if(affected > 0){
					return true;
			} else {
				return false;
			}
		}
		catch(SQLException e) {
			System.err.println(e);
			return false;
		}
	}

	

	
	/**
	 * functions for parkinglot table :: after model modification
	 * @param arr
	 * @return
	 * @throws SQLException
	 */
	public static boolean updateAlloc(ArrayList<Object> arr) throws SQLException {
		int newX = (int) arr.get(1);
		int lotID = (int)arr.get(0);
		//String sql1 = "truncate parking";
		String sql2 =
				"UPDATE parkinglot SET x = ? where lotID = ? "+ 
				"and (select count(*) from parking where lotNo = lotID)=0";
				
		// Create statement object
		
		// Set auto-commit to false
		conn.setAutoCommit(false);
		try {
			
			PreparedStatement stmt2 = conn.prepareStatement(sql2);
			
			stmt2.setInt(1,newX);
			stmt2.setInt(2,lotID);
			
		    stmt2.executeUpdate();
		    conn.commit();
		    int affected = stmt2.executeUpdate();
			if(affected > 0){
					return true;
			} else {
				return false;
			}
		} catch (Exception ex) {
		    conn.rollback();
		    
		}
		return false;
}

	
	
	
}
